<?php
/**
 * User: xuanbang.wen
 * Date: 2019/8/17
 */

namespace App\Web\Model;

use App\Web\Facades\UserAuth;
use EchoPHP\Facades\Db;
use EchoPHP\Validator\Validator;
use App\Web\Model\User as UserModel;

class Coin extends Model {

    /**
     * 用户使用
     */
    const TYPE_USER_USE = '01';
    /**
     * 系统添加
     */
    const TYPE_SYS_ADD = '11';
    /**
     * 系统扣除
     */
    const TYPE_SYS_MINUS = '12';

    /**
     * 查询明细总数
     * @param array $search
     * @return mixed
     */
    public function getListCount($search = [], $teamCode = null) {
        $field = 'count(1) as count';
        return $this->search($field, $search, 0, 0, '', $teamCode)[0]['count'];
    }

    /**
     * 查询下载数明细列表
     * @param $field
     * @param array $search
     * @param int $rowFrom
     * @param int $rowTo
     * @param string $orderBy
     * @return array
     */
    public function search($field = '', $search = [], $rowFrom = 0, $rowTo = 0, $orderBy = '', $teamCode = null) {
        $field = empty($field) ? 'id,userid,username,type,count,oprid,oprname,approver,memo,createdate,updatedate' : $field;
        $sql = 'select ' . $field . ' from DownloadCountDetail';
        $where = $var = [];
        if (isset($search['username']) && !empty($search['username']) && is_numeric($search['username'])) {
            $where[] = 'userid = ?';
            $var[] = $search['username'];
        } elseif (isset($search['username']) && !empty($search['username'])) {
            $where[] = 'username LIKE ?';
            $var[] = '%' . $search['username'] . '%';
        }

        if (isset($search['oprname']) && !empty($search['oprname']) && is_numeric($search['oprname'])) {
            $where[] = 'oprid = ?';
            $var[] = $search['oprname'];
        } elseif (isset($search['oprname']) && !empty($search['oprname'])) {
            $where[] = 'oprname LIKE ?';
            $var[] = '%' . $search['oprname'] . '%';
        }

        if (isset($search['type']) && in_array($search['type'], ['01', '11', '12'])) {
            $where[] = 'type = ?';
            $var[] = $search['type'];
        }

        if (isset($teamCode) && !empty($teamCode)) {
            $where[] = 'oprteamcode like ?';
            $var[] = $teamCode . '%';
        }

        if (isset($search['datestart']) && !empty($search['datestart'])) {
            $where[] = 'createdate > ? ';
            $var[] = date('Y-m-d', strtotime($search['datestart'])) . ' 00:00:00';
        }

        if (isset($search['dateend']) && !empty($search['dateend'])) {
            $where[] = 'createdate < ?';
            $var[] = date('Y-m-d', strtotime($search['dateend'])) . ' 23:59:59';
        }

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= empty($orderBy) ? ' ORDER BY id DESC' : (' ORDER BY ' . $orderBy);
        return Db::select($sql, $var, $rowFrom, $rowTo);
    }

    /**
     * 下载数增减
     * @param $oprId
     * @param $oprName
     * @param $userId
     * @param $type
     * @param $coinCount
     * @param $approver
     * @param $memo
     * @return array
     * @throws \Exception
     */
    public function sysChangeCoin($oprId, $oprName, $userId, $type, $coinCount, $approver, $memo) {
        $userModel = new UserModel();
        if ($userModel->isTeamMember($userId) == false) {
            return [
                'status' => false,
                'message' => '执行失败',
                'validateFailure' => ['您无权编辑该用户数据']
            ];
        }
        $type = $type == 1 ? self::TYPE_SYS_ADD : self::TYPE_SYS_MINUS;
        $rule = [
            'coincount|下载数数目' => [
                'required', 'number', 'min' => 1
            ],
            'approver|审批人' => [
                'required', 'maxLength' => 20
            ],
            'memo|备注' => [
                'maxLength' => 1000
            ],
        ];
        $message = [
            'coincount' => '下载数数目请输入正整数'
        ];
        $validate = new Validator($rule, $message);

        $validateResult = $validate->validate([
            'coincount' => $coinCount,
            'approver' => $approver,
            'memo' => $memo,
        ]);
        if (!$validateResult) {
            return ['status' => false, 'message' => '字段校验失败', 'validateFailure' => $validate->getErrors()];
        }

        $sqlStatement = $this->prepareCoinStatement($oprId, $oprName, $userId, $type, $coinCount, $approver, $memo);
        if ($sqlStatement['status'] != 0) {
            return ['status' => false, 'message' => $sqlStatement['message']];
        }

        $result = Db::connect()->prepareTransactionStatement(true, ...$sqlStatement['statements'])->execute();
        if ($result) {
            return ['status' => true, 'message' => '操作成功'];
        }
        return ['status' => false, 'message' => '操作失败'];
    }

    /**
     * 下载数增减事务脚本
     * @param $oprId
     * @param $oprName
     * @param $userId
     * @param $type
     * @param $coinCount
     * @param $approver
     * @param $memo
     * @return array
     */
    final public function prepareCoinStatement($oprId, $oprName, $userId, $type, $coinCount, $approver, $memo) {
        $coinCount = intval($coinCount);
        $userModel = new UserModel();
        $userInfo = $userModel->getInfo($userId, 'userid, cname, downloadcount, teamcode, teamid');
        if (empty($userInfo)) {
            return ['status' => -1, 'message' => '该用户不存在'];
        }
        switch ($type) {
            case self::TYPE_SYS_ADD:
                $handleType = 'add';
                if ($userInfo['downloadcount'] + $coinCount > 2147483647) {
                    return ['status' => -4, 'message' => '增加下载数超过上限'];
                }
                break;
            case self::TYPE_SYS_MINUS:
            case self::TYPE_USER_USE:
                if ($coinCount > $userInfo['downloadcount']) {
                    return ['status' => -3, 'message' => '该用户剩余下载数已不足扣减'];
                }
                $handleType = 'minus';
                break;
            default:
                return ['status' => -2, 'message' => 'type类型不正确'];
        }
        $userVar = [];
        if ($handleType == 'add') {
            $userSql = 'UPDATE User SET ' . (!empty($approver) ? 'approver=?,' : '') . 'downloadcount = downloadcount+? WHERE userid=?';
            if (!empty($approver)) {
                $userVar[] = $approver;
            }
            $userVar = array_merge($userVar, [$coinCount, $userId]);
        } else {
            $userSql = 'UPDATE User SET ' . (!empty($approver) ? 'approver=?,' : '') . 'downloadcount = downloadcount-? WHERE userid=? AND downloadcount>=?';
            if (!empty($approver)) {
                $userVar[] = $approver;
            }
            $userVar = array_merge($userVar, [$coinCount, $userId, $coinCount]);
        }
        $userStatement = Db::connect()->prepareStatement($userSql, $userVar);

        $coinSql = 'INSERT INTO DownloadCountDetail (userid, username,type, count, oprid,oprname, approver, memo, createdate, updatedate, oprteamcode, oprteamid) VALUES (?,?,?,?,?,?,?,?,Now(),Now(), ?, ?)';
        $coinVar = [$userInfo['userid'], $userInfo['cname'], $type, $coinCount, $oprId, $oprName, $approver, $memo, $userInfo['teamcode'], $userInfo['teamid']];
        $coinStatement = Db::connect()->prepareStatement($coinSql, $coinVar);

        return ['status' => 0, 'statements' => [$userStatement, $coinStatement]];
    }

    /**
     * 查询下载数报表
     * @param string $teamCode
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     */
    public function getCoinReport($teamCode, $startDate, $endDate, $rowFrom, $rowTo, $search = null) {
        $sql = 'select oprteamid , oprteamcode, Team.teamname as oprteamname, 

	SUM(CASE type WHEN \'11\' THEN count ELSE 0 END) AS \'add\',
	SUM(CASE type WHEN \'12\' THEN count ELSE 0 END) AS \'reduce\',
	SUM(CASE type WHEN \'01\' THEN count ELSE 0 END) AS \'used\'

 From DownloadCountDetail left join Team on oprteamid = teamid ';
        $where = $var = [];
        $where[] = 'status = ?';
        $var[] = '01';
        if (!empty($search['teamcode'])) {
            $where[] = 'oprteamcode = ?';
            $var[] = $search['teamcode'];
        }
        if (!empty($teamCode)) {
            $where[] = 'oprteamcode like ?';
            $var[] = $teamCode . "%";
        }

        if (!empty($startDate)) {
            $where[] = 'DownloadCountDetail.createdate >= ?';
            $var[] = $startDate . ' 00:00:00';
        }
        if (!empty($endDate)) {
            $where[] = 'DownloadCountDetail.createdate <= ?';
            $var[] = $endDate . ' 23:59:59';
        }

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' group by oprteamid order by oprteamcode asc ';
        return Db::select($sql, $var, $rowFrom, $rowTo);
    }

    /**
     * 查询下载数报表数目
     * @param string $teamCode
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     */
    public function getCoinReportCount($teamCode, $startDate, $endDate, $search = null) {
        $sql = 'select count(p.oprteamid) as count from (
select oprteamid , 

	SUM(CASE type WHEN \'11\' THEN count ELSE 0 END) AS \'系统新增\',
	SUM(CASE type WHEN \'12\' THEN count ELSE 0 END) AS \'系统扣除\',
	SUM(CASE type WHEN \'01\' THEN count ELSE 0 END) AS \'用户使用\'

 From DownloadCountDetail left join Team on oprteamid = teamid';
        $where = $var = [];
        $where[] = 'status = ?';
        $var[] = '01';
        if (!empty($search['teamcode'])) {
            $where[] = 'oprteamcode = ?';
            $var[] = $search['teamcode'];
        }
        if (!empty($teamCode)) {
            $where[] = 'oprteamcode like ?';
            $var[] = $teamCode . "%";
        }

        if (!empty($startDate)) {
            $where[] = 'DownloadCountDetail.createdate >= ?';
            $var[] = $startDate . ' 00:00:00';
        }
        if (!empty($endDate)) {
            $where[] = 'DownloadCountDetail.createdate <= ?';
            $var[] = $endDate . ' 23:59:59';
        }

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' group by oprteamid) as p ';
        return Db::select($sql, $var);
    }

    /**
     * 查询下载数报表
     * @param string $teamCode
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     */
    public function getCoinReportTeam($teamCode, $userID, $startDate, $endDate, $rowFrom, $rowTo) {
        if (UserAuth::getLoginInfo('issuper') != 1 && substr($teamCode, 0, 5) != substr(UserAuth::getLoginInfo('teamcode'), 0, 5)) {
            return [];
        }
        $sql = 'select userid , username,oprteamid,oprteamcode, Team.teamname as oprteamname, 

	SUM(CASE type WHEN \'11\' THEN count ELSE 0 END) AS \'add\',
	SUM(CASE type WHEN \'12\' THEN count ELSE 0 END) AS \'reduce\',
	SUM(CASE type WHEN \'01\' THEN count ELSE 0 END) AS \'used\'

 From DownloadCountDetail left join Team on oprteamid = teamid';
        $where = $var = [];
        $where[] = 'status = ?';
        $var[] = '01';
        if (!empty($teamCode)) {
            $where[] = 'oprteamcode like ?';
            $var[] = $teamCode . "%";
        }

        if (!empty($userID)) {
            if (is_numeric($userID)) {
                $where[] = 'userid = ?';
                $var[] = $userID . "%";
            } else {
                $where[] = 'username like ?';
                $var[] = "%" . $userID . "%";
            }

        }

        if (!empty($startDate)) {
            $where[] = 'DownloadCountDetail.createdate >= ?';
            $var[] = $startDate . ' 00:00:00';
        }
        if (!empty($endDate)) {
            $where[] = 'DownloadCountDetail.createdate <= ?';
            $var[] = $endDate . ' 23:59:59';
        }

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' group by userid order by userid asc ';
        return Db::select($sql, $var, $rowFrom, $rowTo);
    }

    /**
     * 查询下载数报表数目
     * @param string $teamCode
     * @param int $rowFrom
     * @param int $rowTo
     * @return array
     */
    public function getCoinReportCountTeam($teamCode, $userID, $startDate, $endDate) {
        if (UserAuth::getLoginInfo('issuper') != 1 && substr($teamCode, 0, 5) != substr(UserAuth::getLoginInfo('teamcode'), 0, 5)) {
            return [['count' => 0]];
        }
        $sql = 'select count(p.userid) as count from (
select userid , 

	SUM(CASE type WHEN \'11\' THEN count ELSE 0 END) AS \'系统新增\',
	SUM(CASE type WHEN \'12\' THEN count ELSE 0 END) AS \'系统扣除\',
	SUM(CASE type WHEN \'01\' THEN count ELSE 0 END) AS \'用户使用\'

 From DownloadCountDetail left join Team on oprteamid = teamid';
        $where = $var = [];
        $where[] = 'status = ?';
        $var[] = '01';
        if (!empty($teamCode)) {
            $where[] = 'oprteamcode like ?';
            $var[] = $teamCode . "%";
        }

        if (!empty($userID)) {
            if (is_numeric($userID)) {
                $where[] = 'userid = ?';
                $var[] = $userID . "%";
            } else {
                $where[] = 'username like ?';
                $var[] = "%" . $userID . "%";
            }

        }

        if (!empty($startDate)) {
            $where[] = 'DownloadCountDetail.createdate >= ?';
            $var[] = $startDate . ' 00:00:00';
        }
        if (!empty($endDate)) {
            $where[] = 'DownloadCountDetail.createdate <= ?';
            $var[] = $endDate . ' 23:59:59';
        }

        if (!empty($where)) {
            $sql .= ' WHERE ' . implode(' AND ', $where);
        }

        $sql .= ' group by userid) as p ';
        return Db::select($sql, $var);
    }


}